package com.zhongge.model.dao.impl;

import com.zhongge.model.dao.CategoryDao;
import com.zhongge.model.entity.Category;
import com.zhongge.util.JDBCUtil;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

/**
 * @ClassName CategoryDaoImpl
 * @Description TODO
 * @Author zhongge
 * @Version 1.0
 */
public class CategoryDaoImpl implements CategoryDao {
    @Override
    public List<Category> findByUserId(Integer userId) {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        List<Category> categories = new ArrayList<>();

        try {
            conn = JDBCUtil.getConnection();
            String sql = "SELECT * FROM category WHERE user_id = ? ORDER BY create_time DESC";
            pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1, userId);
            rs = pstmt.executeQuery();

            while (rs.next()) {
                Category category = new Category();
                category.setId(rs.getInt("id"));
                category.setName(rs.getString("name"));
                category.setDescription(rs.getString("description"));
                category.setUserId(rs.getInt("user_id"));
                category.setCreatedTime(rs.getTimestamp("created_time"));
                category.setUpdatedTime(rs.getTimestamp("updated_time"));
                categories.add(category);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JDBCUtil.close(conn, pstmt, rs);
        }

        return categories;
    }

    @Override
    public Category findById(Integer id) {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        Category category = null;

        try {
            conn = JDBCUtil.getConnection();
            String sql = "SELECT * FROM category WHERE id = ?";
            pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1, id);
            rs = pstmt.executeQuery();

            if (rs.next()) {
                category = new Category();
                category.setId(rs.getInt("id"));
                category.setName(rs.getString("name"));
                category.setDescription(rs.getString("description"));
                category.setUserId(rs.getInt("user_id"));
                category.setCreatedTime(rs.getTimestamp("created_time"));
                category.setUpdatedTime(rs.getTimestamp("updated_time"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JDBCUtil.close(conn, pstmt, rs);
        }

        return category;
    }

    @Override
    public int insert(Category category) {
        Connection conn = null;
        PreparedStatement pstmt = null;
        int rows = 0;

        try {
            conn = JDBCUtil.getConnection();
            String sql = "INSERT INTO category(name, description, user_id, create_time, update_time) VALUES(?,?, ?, ?, ?)";
            pstmt = conn.prepareStatement(sql);

            Date now = new Date();
            pstmt.setString(1, category.getName());
            pstmt.setString(2, category.getDescription());
            pstmt.setInt(3, category.getUserId());
            pstmt.setTimestamp(4, new java.sql.Timestamp(now.getTime()));
            pstmt.setTimestamp(5, new java.sql.Timestamp(now.getTime()));

            rows = pstmt.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JDBCUtil.close(conn, pstmt, null);
        }
        return rows;
    }

    @Override
    public int update(Category category) {
        Connection conn = null;
        PreparedStatement pstmt = null;
        int rows = 0;

        try {
            conn = JDBCUtil.getConnection();
            String sql = "UPDATE category SET name = ?, update_time = ? WHERE id = ?";
            pstmt = conn.prepareStatement(sql);

            pstmt.setString(1, category.getName());
            pstmt.setTimestamp(2, new java.sql.Timestamp(new Date().getTime()));
            pstmt.setInt(3, category.getId());

            rows = pstmt.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JDBCUtil.close(conn, pstmt, null);
        }

        return rows;
    }

    @Override
    public int delete(Integer id) {
        Connection conn = null;
        PreparedStatement pstmt = null;
        int rows = 0;

        try {
            conn = JDBCUtil.getConnection();
            String sql = "DELETE FROM category WHERE id = ?";
            pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1, id);

            rows = pstmt.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JDBCUtil.close(conn, pstmt, null);
        }

        return rows;
    }

    @Override
    public boolean checkNameExists(Integer userId, String name) {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;

        try {
            conn = JDBCUtil.getConnection();
            String sql = "SELECT id FROM category WHERE user_id = ? AND name = ?";
            pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1, userId);
            pstmt.setString(2, name);
            rs = pstmt.executeQuery();

            return rs.next(); // 如果有结果，说明已存在
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JDBCUtil.close(conn, pstmt, rs);
        }

        return false;
    }
}
